跳到主要内容

MySQL 学习（5）InnoDB Insert Buffer（插入缓冲）

转载自 InnoDB引擎之-插入缓冲（Insert Buffer）

插入缓冲（Insert Buffer）是什么？​

先说结论：Insert Buffer 就是用于提升非聚集索引页的插入性能的，其数据结构类似于数据页的一个 B+ 树，物理存储在共享表空间 ibdata1 中。

在 InnoDB 引擎上进行插入操作时，一般需要按照主键顺序进行插入，这样才能获得较高的插入性能。当一张表中存在非聚簇的且不唯一的索引时，在插入时，数据页的存放还是按照主键进行顺序存放，但是对于非聚簇索引叶节点的插入不再是顺序的了，这时就需要离散的访问非聚簇索引页，由于随机读取的存在导致插入操作性能下降。

InnoDB 为此设计了 Insert Buffer 来进行插入优化。

对于非聚簇索引的插入或者更新操作，不是每一次都直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，

  • 若在，则直接插入；
  • 若不在，则先放入到一个 Insert Buffer 中。

看似数据库这个非聚集的索引已经查到叶节点，而实际没有，这时存放在另外一个位置。然后再以一定的频率和情况进行 Insert Buffer 和非聚簇索引页子节点的合并操作。这时通常能够将多个插入合并到一个操作中，这样就大大提高了对于非聚簇索引的插入性能。

补充01：非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的 B+树索引，称之为非聚集索引。非聚集索引与聚集索引的区别在于 非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

补充02：看到上图，可能大家会认为 Insert Buffer 就是 InnoDB 缓冲池的一个组成部分。其实对也不对，InnoDB 缓冲池确实包含了 Insert Buffer的信息，但 Insert Buffer 其实和数据页一样，也是物理存在的（以 B+ 树的形式存在共享表空间中）。

Insert Buffer 的作用​

先说几个点：

  • 一张表只能有一个主键索引，那是因为其物理存储是一个 B+ 树。（别忘了聚集索引叶子节点存储的数据，而数据只有一份）
  • 非聚集索引叶子节点存的是聚集索引的主键

Insert Buffer 插入过程​

聚集索引插入​

在 InnoDB 存储引擎中，主键是行唯一的标识符（聚集索引）。

插入数据一般都是按照主键递增插入，因此聚集索引都是顺序的，不需要磁盘的随机读取。

比如表：

CREATE TABLE test(
  id INT AUTO_INCREMENT,
  name VARCHAR(30),
  PRIMARY KEY(id)
);

上表中主键 id，它有以下的特性：

  • id 列是自增长的
  • id 列插入 NULL 值时，由于 AUTO_INCREMENT 的原因，其值会递增
  • 同时数据页中的行记录按id的值进行顺序存放

一般情况下由于聚集索引的有序性，不需要随机读取页中的数据，顺序插入速度是非常快的。

但如果把列 id 插入 UUID 这种数据，那插入就是和非聚集索引一样都是随机的了。会导致 B+ tree 结构不停地变化，那性能必然会受到影响。

非聚集索引插入​

表中一般有很多非聚集索引，比如按照 b字段查询，且 b字段不是唯一的。如下表：

CREATE TABLE test(
 id INT AUTO_INCREMENT,
 name VARCHAR(30),
 PRIMARY KEY(id),
 KEY(name)
);

如上表：

  • 有一个聚集索引 id
  • 有一个不唯一的非聚集索引 name
  • 在插入数据时数据页是按照主键 id 进行顺序存放
  • 辅助索引 name 的数据插入不是顺序的

非聚集索引也是一颗 B+ 树，只是叶子节点存的是聚集索引的主键和 name 的值。因为不能保证 name 列的数据是顺序的，所以非聚集索引这棵树的插入必然也不是顺序的了。当然如果 name 列插入的是时间类型数据，那其非聚集索引的插入也是顺序的。

可以看出非聚集索引插入的离散性导致了插入性能的下降，因此 InnoDB 引擎设计了 Insert Buffer 来提高插入性能 。

Insert Buffer 插入过程​

1、首先对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中。

2、若在，则直接插入；若不在，则先放入到一个 Insert Buffer 对象中。

3、给外部的感觉好像是树已经插入非聚集的索引的叶子节点，而其实是存放在其他位置了

以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge（合并）操作，通常会将多个插入操作一起进行 merge，这就大大的提升了非聚集索引的插入性能。

Insert Buffer 的使用要求​

  • 索引是非聚集索引
  • 索引不是唯一（unique）的

只有满足上面两个必要条件时，InnoDB 存储引擎才会使用 Insert Buffer 来提高插入性能。

那为什么必须满足上面两个条件呢？

第一点索引是非聚集索引就不用说了，人家聚集索引本来就是顺序的也不需要你

第二点必须不是唯一（unique）的，因为在写入 Insert Buffer 时，数据库并不会去判断插入记录的唯一性。如果再去查找肯定又是离散读取的情况了，这样 Insert Buffer 就失去了意义。

Insert Buffer 信息查看​

可以使用命令 SHOW ENGINE INNODB STATUS 来查看 Insert Buffer 的信息：

SHOW ENGINE INNODB STATUS;
mysql> show engine innodb status
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 7545, free list len 3790, seg size 11336, 
8075308 inserts,7540969 merged sec, 2246304 merges
...

使用命令后，我们会看到很多信息，这里我们只看下 INSERT BUFFER 的:

seg size 代表当前 Insert Buffer 的大小 11336 * 16KB free listlen 代表了空闲列表的长度 size 代表了已经合并记录页的数量 Inserts 代表了插入的记录数 merged recs 代表了合并的插入记录数量 merges 代表合并的次数，也就是实际读取页的次数

merges：merged recs 大约为1 : 3，代表了 Insert Buffer 将对于非聚集索引页的离散 IO 逻辑请求大约降低了 2/3

Insert Buffer 的问题​

说了这么多针对于 Insert Buffer 的好处，但目前 Insert Buffer 也存在一个问题：

即在写密集的情况下，插入缓冲会占用过多的缓冲池内存（innodb_buffer_pool），默认最大可以占用到 1/2 的缓冲池内存。

占用了过大的缓冲池必然会对其他缓冲池操作带来影响

Insert Buffer 的优化​

MySQL5.5 之前的版本中其实都叫做 Insert Buffer，之后优化为 Change Buffer 可以看做是 Insert Buffer 的升级版。

插入缓冲（Insert Buffer）这个其实只针对 INSERT 操作做了缓冲，而 Change Buffer 对 INSERT、DELETE、UPDATE 都进行了缓冲，所以可以统称为写缓冲，其可以分为：

  • Insert Buffer
  • Delete Buffer
  • Purgebuffer

总结：​

Insert Buffer 到底是个什么？

  • 其实 Insert Buffer 的数据结构就是一棵 B+ 树。
  • 在 MySQL 4.1 之前的版本中每张表有一棵 Insert Buffer B+ 树
  • 目前版本是全局只有一棵 Insert Buffer B+ 树，负责对所有的表的辅助索引进行 Insert Buffer
  • 这棵 B+ 树存放在共享表空间 ibdata1 中

以下几种情况下 Insert Buffer 会写入真正非聚集索引，也就是所说的 Merge Insert Buffer

  • 当辅助索引页被读取到缓冲池中时
  • Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时
  • Master Thread 线程中每秒或每 10 秒会进行一次 Merge Insert Buffer 的操作

一句话概括下：Insert Buffer 就是用于提升非聚集索引页的插入性能的，其数据结构类似于数据页的一个 B+ 树，物理存储在共享表空间 ibdata1 中。

Reference​

参考资料 重要，知识点：InnoDB的插入缓冲 参考资料 InnoDB存储引擎第2版